import asyncio
import time
import ygg_aiomysql as aiomysql

async def main():
    """
    示例08: 创建MySQL表
    展示如何创建数据库、定义表结构、调用add_table方法创建表、验证表结构的完整流程
    """
    # 使用时间戳生成唯一数据库名和表名
    test_db_name = f"test_db_{int(time.time())}"
    test_table_name = f"test_table_{int(time.time())}"
    
    # 创建临时连接池
    pool = await aiomysql.new_pool_env()
    
    # 创建测试数据库
    await aiomysql.add_database(pool, test_db_name)

    
    # 定义表结构
    columns = {
        "id": "INT PRIMARY KEY AUTO_INCREMENT",
        "name": "VARCHAR(50) NOT NULL",
        "created_at": "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    }
    
    # 调用add_table方法创建表
    print(f"正在创建表 {test_table_name}...")
    await aiomysql.add_table(pool, test_table_name, columns)
    print(f"表 {test_table_name} 创建成功")
    
    # 验证表已创建且结构正确
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("USE test")
            
            # 验证表存在
            await cur.execute(f"SHOW TABLES LIKE '{test_table_name}'")
            result = await cur.fetchone()
            print(f"验证表存在: {'成功' if result else '失败'}")
            
            # 验证表结构
            await cur.execute(f"DESCRIBE {test_table_name}")
            columns_info = await cur.fetchall()
            print(f"表包含 {len(columns_info)} 列")
            
            # 检查各列定义
            column_names = [col[0] for col in columns_info]
            print(f"列名: {', '.join(column_names)}")
    
    # 关闭连接池
    pool.close()
    await pool.wait_closed()
    
    # 清理测试数据库
    print(f"清理测试数据库 {test_db_name}...")
    pool = await aiomysql.new_pool_env()
    await aiomysql.delete_database(pool, test_db_name)
    pool.close()
    print("清理完成")

if __name__ == "__main__":
    asyncio.run(main())